/*

select  * FROM Quotation_Table
*/
ALTER  PROC sp_load_quotations_to_change 
(
	@Load_Expired INT, 
	@Quote_No VARCHAR(50), 
	@Part_Number VARCHAR(50), 
	@Cust_Id INT, 
	@USER_ID VARCHAR(50),
	@PO_Number VARCHAR(50),
	@End_Customer_ID VARCHAR(50),
	@From_Date VARCHAR(20),
	@To_Date VARCHAR(20)
)
AS

BEGIN
	DECLARE @results Table	(
					Quotation_No VARCHAR(50), 
					Quotation_ID INT, 
					Q_Customer_PO_Number VARCHAR(50), 
					Q_Booking_Date DATETIME, 
					Customer_ID INT, 
					Expiry_Date DATETIME, 
					Is_Expired INT, 
					Is_Approved INT, 
					Is_Closed INT, 
					Is_Totally_Approved INT,
					Is_Totally_Transferred INT,
					IS_CANCELLED INT,
					CUSTOMER_NAME VARCHAR(50), 
					MFG_PART_NUMBER VARCHAR(50), 
					END_CUSTOMER VARCHAR(50), 
					END_USER VARCHAR(50), 
					QTY INT, 
					UNIT_COST FLOAT, 
					SUGG_RESALE_PRICE FLOAT,
					QUOTE_TYPE VARCHAR(50),
					Q_NEW_END_USER VARCHAR(50),
					Q_NEW_END_CUSTOMER VARCHAR(50)
				)

	
	DECLARE @Quotation_No VARCHAR(50), @Quotation_ID INT, 
		@Q_Customer_PO_Number VARCHAR(50), @Q_Booking_Date DATETIME, 
		@Customer_ID INT, @Expiry_Date DATETIME, @Is_Expired INT, @Is_Approved INT, @Is_Totally_Approved INT, @Is_Totally_Transferred INT
	DECLARE @CUSTOMER_NAME VARCHAR(50)
	DECLARE @MFG_PART_NUMBER VARCHAR(50) 
	DECLARE @END_CUSTOMER VARCHAR(50)
	DECLARE @END_USER VARCHAR(50)
	DECLARE @QTY INT
	DECLARE @UNIT_COST FLOAT
	DECLARE @SUGG_RESALE_PRICE FLOAT
	DECLARE @QUOTE_TYPE VARCHAR(50)
	DECLARE @Q_NEW_END_USER VARCHAR(50)
	DECLARE @Q_NEW_END_CUSTOMER VARCHAR(50)

	DECLARE @USER_TYPE INT
	SET @USER_TYPE= 0 --CHIPTRAC USER

	SELECT @USER_TYPE = USER_TYPE FROM USER_TABLE WHERE USER_ID = @USER_ID

	DECLARE @LINE_ITEM_CURSOR_STRING NVARCHAR(2000)
	SET @LINE_ITEM_CURSOR_STRING = N'DECLARE line_item_cursor CURSOR FOR SELECT  QT.Quotation_No, QT.Quotation_ID, QT.Q_Customer_PO_Number, convert(varchar,QT.Q_Booking_Date,101) Q_Booking_Date, QT.Customer_ID, QT.Expiry_Date,datediff(day,current_timestamp, QT.Expiry_Date) as Is_Expired , C.C_NAME, P.PART_NUMBER, (SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = QT.Q_ODM_ID) AS END_CUSTOMER, (SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = QT.Q_ENDCUSTOMER_ID) AS END_USER, QLI.QLI_QTY, QLI.QLI_UNIT_COST, QLI.SUGG_RESALE_PRICE, QT.Q_QUOTE_TYPE, QT.Q_NEW_END_USER, QT.Q_NEW_END_CUSTOMER, QLI.is_approved 
											FROM 
												Quotation_Table QT, Customer C, PRODUCT P, Quotation_Order_LineItem QLI
											WHERE QLI.QUOTATION_ID = QT.QUOTATION_ID
												AND P.PRODUCT_ID = QLI.PRODUCT_ID
												AND C.CUSTOMER_ID = QT.CUSTOMER_ID '

	IF ( @Load_Expired = 0 )
	BEGIN
		SET @LINE_ITEM_CURSOR_STRING = @LINE_ITEM_CURSOR_STRING + ' AND Expiry_Date > CURRENT_TIMESTAMP'
	END
	ELSE
	BEGIN
		SET @LINE_ITEM_CURSOR_STRING = @LINE_ITEM_CURSOR_STRING + ' AND Expiry_Date is not null'
	END

	IF ( @Cust_Id <> 0 )
	BEGIN
		SET @LINE_ITEM_CURSOR_STRING = @LINE_ITEM_CURSOR_STRING + ' AND QT.Customer_Id = ' + CONVERT(VARCHAR,@Cust_Id)
	END
	ELSE --All Customers
	BEGIN
		IF ( @USER_TYPE = 3 ) --SALES PERSON, NEED TO LOAD SALES PERSON RELATED CUSTOMER QUOTES ONLY
		BEGIN
			DECLARE @SALESPERSON_ID INT
			SELECT @SALESPERSON_ID = SALESPERSON_ID FROM USER_MAP_TABLE WHERE USER_ID = @USER_ID
			--SET @LINE_ITEM_CURSOR_STRING = @LINE_ITEM_CURSOR_STRING + ' AND QT.Customer_Id IN (SELECT CUSTOMER_ID FROM CUSTOMER_SALESPERSON_RELATIONSHIP WHERE SALESPERSON_ID = ' + CONVERT(VARCHAR,@SALESPERSON_ID) + ' AND RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]=''Active''))'
			SET @LINE_ITEM_CURSOR_STRING = @LINE_ITEM_CURSOR_STRING + ' AND QT.Customer_Id IN (SELECT CUSTOMER_ID FROM CUSTOMER_SALESPERSON_RELATIONSHIP WHERE SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES(' + CONVERT(VARCHAR,@SALESPERSON_ID) + ')) AND RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]=''Active''))'
		END
	END
	IF ( @Quote_No <> '0' )
	BEGIN
		SET @LINE_ITEM_CURSOR_STRING = @LINE_ITEM_CURSOR_STRING + ' AND Quotation_No LIKE ''' + @Quote_No + '%' + ''''
	END
	IF ( @PO_Number <> '' )
	BEGIN
		SET @LINE_ITEM_CURSOR_STRING = @LINE_ITEM_CURSOR_STRING + ' AND Q_Customer_PO_Number LIKE ''' + @PO_Number + '%' + ''''
	END
	IF ( @End_Customer_ID <> '0' )
	BEGIN
		SET @LINE_ITEM_CURSOR_STRING = @LINE_ITEM_CURSOR_STRING + ' AND Q_ODM_ID = ' + CONVERT( VARCHAR, @End_Customer_ID)
	END
	IF ( @From_Date <> '' )
	BEGIN
		SET @LINE_ITEM_CURSOR_STRING = @LINE_ITEM_CURSOR_STRING + ' AND Q_Booking_Date >= ''' + @From_Date + ''''
	END
	IF ( @To_Date <> '' )
	BEGIN
		SET @LINE_ITEM_CURSOR_STRING = @LINE_ITEM_CURSOR_STRING + ' AND Q_Booking_Date <= ''' + @To_Date + ''''
	END

	PRINT ' @LINE_ITEM_CURSOR_STRING = ' + @LINE_ITEM_CURSOR_STRING

	EXECUTE SP_EXECUTESQL @LINE_ITEM_CURSOR_STRING

	OPEN line_item_cursor
	fetch next from line_item_cursor into @Quotation_No, @Quotation_ID, @Q_Customer_PO_Number, @Q_Booking_Date, @Customer_ID, @Expiry_Date, @Is_Expired, @CUSTOMER_NAME, @MFG_PART_NUMBER, @END_CUSTOMER, @END_USER, @QTY, @UNIT_COST, @SUGG_RESALE_PRICE, @QUOTE_TYPE, @Q_NEW_END_USER, @Q_NEW_END_CUSTOMER, @is_approved
	DECLARE @count INT
	DECLARE @count_closed INT	
	DECLARE @count_products INT
	DECLARE @Is_Closed INT
	DECLARE @IS_CANCELLED INT
	DECLARE @Product_Id INT
	DECLARE @count_hold INT
	DECLARE @count_not_transferred INT
	DECLARE @count_not_cancelled INT
		
	WHILE @@fetch_status = 0
	BEGIN
		--SELECT @Is_Approved = Is_approved FROM Quotation_Order_LineItem WHERE Quotation_Id = @Quotation_Id AND Is_Approved = 0 AND Transfer <> 0
		SELECT @count_closed = COUNT(*) FROM Quotation_Order_LineItem WHERE Quotation_Id = @Quotation_Id AND Transfer = 1 AND ISNULL(QLI_IS_CANCEL,1) <> 0
		SELECT @count_hold = count(*) FROM QUOTATION_ORDER_LINEITEM WHERE QUOTATION_ID = @Quotation_Id AND IS_APPROVED = 1 AND ISNULL(QLI_IS_CANCEL,1) <> 0
		SELECT @count_not_transferred = count(*) FROM QUOTATION_ORDER_LINEITEM WHERE QUOTATION_ID = @Quotation_Id AND QLI_Qty > 0 AND Is_Approved = 0 AND Transfer <> 0 AND ISNULL(QLI_IS_CANCEL,1) <> 0
		SELECT @count_not_cancelled = count(*) FROM QUOTATION_ORDER_LINEITEM WHERE QUOTATION_ID = @Quotation_Id AND Transfer = 1/*not closed*/ AND ISNULL(QLI_IS_CANCEL,1) <> 0
		
	--	IF @count > 0
	--	BEGIN
	--		SET @Is_Approved = 0
	--	END
	--	ELSE
	--	BEGIN
	--		SET @Is_Approved = 1
	--	END

		IF @count_closed > 0
		BEGIN
			SET @Is_Closed = 1
		END
		ELSE
		BEGIN
			SET @Is_Closed = 0
		END

		IF @count_hold > 0
		BEGIN
			SET @Is_Totally_Approved = 1
		END
		ELSE
		BEGIN
			SET @Is_Totally_Approved = 0
		END
		
		IF @count_not_transferred > 0
		BEGIN
			SET @Is_Totally_Transferred = 1
		END
		ELSE
		BEGIN
			SET @Is_Totally_Transferred = 0
		END
		
		IF (@count_not_cancelled > 0 )
		 BEGIN
			SET @IS_CANCELLED = 1 --QUOTATION NOT CANCELLED
	  	 END
		ELSE
		 BEGIN
			SET @IS_CANCELLED = 0 --QUOTATION CANCELLED
		 END


		IF(@Part_Number <> '0')
		BEGIN
			SELECT @Product_Id = Product.Product_ID from product,Process_Step where Part_Number = @Part_Number and product.processStep_id=Process_Step.ProcessStep_ID and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name = 'Finished Goods')

			SELECT @count_products = count(*) FROM Quotation_Order_LineItem WHERE Quotation_Id = @Quotation_Id AND Product_Id = @Product_Id
			IF @count_products > 0
			BEGIN
				INSERT INTO @results values(@Quotation_No, @Quotation_ID, @Q_Customer_PO_Number, @Q_Booking_Date, @Customer_ID, @Expiry_Date, @Is_Expired, @Is_Approved, @Is_Closed, @Is_Totally_Approved, @Is_Totally_Transferred, @IS_CANCELLED, @CUSTOMER_NAME, @MFG_PART_NUMBER, @END_CUSTOMER, @END_USER, @QTY, @UNIT_COST, @SUGG_RESALE_PRICE, @QUOTE_TYPE, @Q_NEW_END_USER, @Q_NEW_END_CUSTOMER)
			END
		END
		ELSE
		BEGIN
			INSERT INTO @results values(@Quotation_No, @Quotation_ID, @Q_Customer_PO_Number, @Q_Booking_Date, @Customer_ID, @Expiry_Date, @Is_Expired, @Is_Approved, @Is_Closed, @Is_Totally_Approved,@Is_Totally_Transferred, @IS_CANCELLED, @CUSTOMER_NAME, @MFG_PART_NUMBER, @END_CUSTOMER, @END_USER, @QTY, @UNIT_COST, @SUGG_RESALE_PRICE, @QUOTE_TYPE, @Q_NEW_END_USER, @Q_NEW_END_CUSTOMER)
		END

		SET @Quotation_No = NULL
		SET @Quotation_ID = NULL
		SET @Q_Customer_PO_Number = NULL
		SET @Q_Booking_Date = NULL
		SET @Customer_ID = NULL
		SET @Expiry_Date =NULL
		SET @Is_Expired = NULL
		SET @Is_Approved = NULL
		SET @IS_CANCELLED = 1
		SET @CUSTOMER_NAME = NULL
		SET @MFG_PART_NUMBER = NULL
		SET @END_CUSTOMER = NULL
		SET @END_USER = NULL
		SET @QTY = NULL
		SET @UNIT_COST = NULL
		SET @SUGG_RESALE_PRICE = NULL
		SET @QUOTE_TYPE = NULL
		
		fetch next from line_item_cursor into @Quotation_No, @Quotation_ID, @Q_Customer_PO_Number, @Q_Booking_Date, @Customer_ID, @Expiry_Date, @Is_Expired, @CUSTOMER_NAME, @MFG_PART_NUMBER, @END_CUSTOMER, @END_USER, @QTY, @UNIT_COST, @SUGG_RESALE_PRICE, @QUOTE_TYPE, @Q_NEW_END_USER, @Q_NEW_END_CUSTOMER, @is_approved
	END
	CLOSE line_item_cursor
	DEALLOCATE line_item_cursor

	SELECT * FROM @results WHERE Is_Closed <> 0 AND IS_CANCELLED <> 0 ORDER BY Quotation_No
END
